﻿using System;
using System.Collections.Generic;
using System.Text;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;
using SdmxMl.Excel;

namespace SasPlugIn.SASCli
{
    /// <summary>
    /// Helper for generating SAS proc SQL code for export of Artefacts content
    /// </summary>
    public class NomenExport
    {
        #region Internal members

        private SmManager sm;
        private string tablePrefix;
        private AnnotationInventory anInventory;
        protected Dictionary<string, CodeListType> dico;

        #endregion

        #region C'tor

        public NomenExport(string tbPrefix)
        {
            tablePrefix = tbPrefix;
        }
        #endregion

        #region Artefact Properties

        /// <summary>Define table columns used to transmit Artefact properties </summary>
        /// <param name="a">the artefact</param>
        /// <param name="sb">the command builder</param>
        private void DefineArtefactFields(Artefact a, StringBuilder sb)
        {
            sb.Append("(AgencyID char(30), ID char(30), Version char(10), ");
            foreach (string lg in LangSupport.SupportedLanguages)
                sb.Append("Name_").Append(lg).Append(" char(120), ");

            foreach (string lg in LangSupport.SupportedLanguages)
                sb.Append("Desc_").Append(lg).Append(" char(2000), ");

            sb.Append("ValidFrom char(10), ValidTo char(10), ");

            // here Annotations selected
            anInventory = new AnnotationInventory();
            anInventory.Inventory(a.AnnotationList, false);

            GenAnnotationHeader(sb);

            // Remove last comma
            sb.Remove(sb.Length - 2, 2);
            sb.Append(" );");
            sb.Append(Environment.NewLine);
        }

        /// <summary>Gen SAS code for Artefact parameters table</summary>
        /// <param name="a">the artefact</param>
        /// <param name="tbName">Name of table in SAS</param>
        /// <returns>SAS instruction codes</returns>
        public string GenSpArtefact(Artefact a, string tbName)
        {
            string sp = string.Empty;
            if (a != null)
            {
                StringBuilder sb = new StringBuilder(256);
                sb.Append("proc sql;   create table ").Append(tablePrefix).Append('.');
                sb.Append(tbName);

                DefineArtefactFields(a, sb);
                sb.Append("insert into ").Append(tablePrefix).Append('.').Append(tbName).Append(Environment.NewLine);

                sb.Append("values(");
                sb.Append(ValChar(a.AgencyId)).Append(',');
                sb.Append(ValChar(a.Id)).Append(',');
                sb.Append(ValChar(a.Version)).Append(',');
                foreach (string lg in LangSupport.SupportedLanguages)
                    sb.Append(ValChar(a.Name.GetForLanguage(lg))).Append(',');
                foreach (string lg in LangSupport.SupportedLanguages)
                    sb.Append(ValChar(a.Description.GetForLanguage(lg))).Append(',');
                sb.Append(ValChar(a.ValidFrom)).Append(',');
                sb.Append(ValChar(a.ValidTo)).Append(',');

                // Annotations ?
                if (anInventory.Dico.Count > 0)
                    AddAnnotations(sb, a.AnnotationList);

                // Remove last comma
                sb.Remove(sb.Length - 1, 1);

                sb.Append(")").Append(Environment.NewLine);
                sb.Append(";");
                sb.Append("quit;");

                sp = sb.ToString();
            }

            return sp;
        }

        #endregion

        #region Annotations helpers

        /// <summary>
        /// Completes header with Annotations header info
        /// </summary>
        /// <param name="sb">the command builder</param>
        private void GenAnnotationHeader(StringBuilder sb)
        {
            if (anInventory.Dico.Count > 0)
            {
                // For each annotation, 6 columns ANN_TYPE, ANN_TITLE, ALNN_TEXT_lg.., ANN_URN
                foreach (KeyValuePair<string, AnnotationCount> kvp in anInventory.Dico)
                {
                    // here check if selected annot
                    if (anInventory.IsSelectedAnnot(kvp.Value.Annot.Type) == false)
                        continue;

                    // For max instances encountered
                    for (int i = 0; i < kvp.Value.Count; i++)
                    {
                        string detail = string.Empty;
                        string instanceCount = string.Empty;
                        string annType = kvp.Value.Annot.Type.Replace(" ", "_");
                        if (kvp.Value.Count > 1)
                            instanceCount = string.Format("_{0}", i + 1);
                        if (kvp.Value.HasTitle)
                        {
                            sb.Append(annType + instanceCount + "_Title").Append(" char(80), ");
                            detail = "_Text";
                        }

                        if (kvp.Value.Internationalized)
                        {
                            foreach (string lg in SdmxMl.Common.LangSupport.SupportedLanguages)
                                sb.Append(annType + instanceCount + detail + "_" + lg).Append(" char(2000), "); ;
                        }
                        else
                        {
                            sb.Append(annType + instanceCount + detail).Append(" char(2000), ");
                        }
                        if (kvp.Value.HasUrn)
                        {
                            sb.Append(annType + instanceCount + "_Url").Append(" char(200), ");
                        }
                    }
                }
            }

        }


        private void AddAnnotations(StringBuilder sb, AnnotationList annotationList)
        {
            // For each annotation, 6 columns ANN_TYPE, ANN_TITLE, ALNN_TEXT_lg..
            foreach (KeyValuePair<string, AnnotationCount> kvp in anInventory.Dico)
            {
                // here check if selected annot
                if (anInventory.IsSelectedAnnot(kvp.Value.Annot.Type) == false)
                    continue;

                for (int i = 0; i < kvp.Value.Count; i++)
                {
                    Annotation a = annotationList.GetAnnotationType(kvp.Value.Annot.Type, i + 1);
                    if (a != null)
                    {
                        if (kvp.Value.HasTitle)
                            sb.Append(ValChar(a.Title)).Append(',');

                        if (kvp.Value.Internationalized)
                        {
                            foreach (string lg in SdmxMl.Common.LangSupport.SupportedLanguages)
                                sb.Append(ValChar(a.Text.GetIfDefinedForLanguage(lg))).Append(',');
                        }
                        else
                            sb.Append(ValChar(a.Text.GetFirst())).Append(',');

                        if (kvp.Value.HasUrn)
                            sb.Append(ValChar(a.Url != null ? a.Url.ToString() : string.Empty)).Append(',');
                    }
                    else
                    {
                        if (kvp.Value.HasTitle)
                            sb.Append(ValChar(string.Empty)).Append(',');


                        if (kvp.Value.Internationalized)
                        {
                            foreach (string lg in SdmxMl.Common.LangSupport.SupportedLanguages)
                                sb.Append(ValChar(string.Empty)).Append(',');
                        }
                        else
                            sb.Append(ValChar(string.Empty)).Append(',');

                        if (kvp.Value.HasUrn)
                            sb.Append(ValChar(string.Empty)).Append(',');
                    }
                }
            }
        }
        #endregion

        #region Codelist

        /// <summary> Gen sas code to export codelist as table </summary>
        /// <param name="clt">Codelist artefact to export in SAS</param>
        /// <param name="tbName">Name of the SAS table to create</param>
        /// <param name="anInv">List of annotations with selected ones</param>
        /// <returns>string for SAS execution</returns>
        public string GenSpCodelist(CodeListType clt, string tbName, AnnotationInventory anInv)
        {
            string sp = string.Empty;
            anInventory = anInv;
            if (clt != null)
            {
                StringBuilder sb = new StringBuilder(256);
                sb.Append("proc sql;   create table ").Append(tablePrefix).Append('.');
                sb.Append(tbName);

                DefineTableFields(sb, false);
                sb.Append("insert into ").Append(tablePrefix).Append('.').Append(tbName).Append(Environment.NewLine);

                foreach (CodeType c in clt.CodeList)
                {
                    sb.Append("values(");
                    sb.Append(ValChar(c.Id)).Append(',');
                    sb.Append(ValChar(c.Parent)).Append(',');
                    sb.Append(ValChar(GetLevelOfCode(clt, c.Id).ToString())).Append(',');
                    foreach (string lg in LangSupport.SupportedLanguages)
                        sb.Append(ValChar(c.Name.GetForLanguage(lg))).Append(',');
                    foreach (string lg in LangSupport.SupportedLanguages)
                        sb.Append(ValChar(c.Description.GetForLanguage(lg))).Append(',');
                    sb.Append(ValChar(clt.ValidFrom)).Append(',');
                    sb.Append(ValChar(clt.ValidTo)).Append(',');

                    // Annotations ?
                    if (anInventory.Dico.Count > 0)
                        AddAnnotations(sb, c.AnnotationList);

                    // Remove last comma
                    sb.Remove(sb.Length - 1, 1);

                    sb.Append(")").Append(Environment.NewLine);
                }
                sb.Append(";");
                sb.Append("quit;");

                sp = sb.ToString();
            }

            return sp;
        }
        #endregion

        #region Hierarchical Codelist

        private Dictionary<string, CodeListType> GetRefDictionary(HierarchyType hierarchy)
        {
            Dictionary<string, CodeListType> dico = new Dictionary<string, CodeListType>();
            foreach (CodelistRefType crt in hierarchy.CodelistRefList)
            {
                CodeListType clType = null;
                Artefact a = sm.GetArtefact(SdmxArtefactType.CodeLists, crt);
                if (a != null)
                    clType = a as CodeListType;
                if (clType != null)
                    dico.Add(crt.Alias, clType);
            }

            return dico;
        }


        public string GenSpHierarchy(HierarchicalCodelistType hcl, HierarchyType hi, string tbName, AnnotationInventory anInv)
        {
            string sp = string.Empty;
            anInventory = anInv;

            if (hcl != null && hi != null)
            {
                sm = ReferenceManager.GetSmContainerImage();

                // Ensure in manager
                ArtefactRefTypedList artl = hcl.ArtefactRefList;
                sm.LoadMissingArtefactsFromReferenceMgr(artl);
                sm.LoadMissingArtefactsFromRegistry(artl);

                dico = GetRefDictionary(hi);

                StringBuilder sb = new StringBuilder(256);
                sb.Append("proc sql;   create table ").Append(tablePrefix).Append('.');
                sb.Append(tbName);

                DefineTableFields(sb, false);

                sb.Append("insert into ").Append(tablePrefix).Append('.').Append(tbName).Append(Environment.NewLine);

                foreach (CodeRefType it in hi.CodeRefList)
                    FillHierarchyRow(sb, string.Empty, it, 0);


                sb.Append(";");
                sb.Append("quit;");
                sp = sb.ToString();
            }

            return sp;
        }

        protected void FillHierarchyRow(StringBuilder sb, string parentCode, CodeRefType crt, int level)
        {
            CodeListType clType = dico[crt.CodelistAliasRef];
            ISdmxHierarchyListItem item = clType.CodeList.LocateItem(crt.CodeID);

            if (item == null)
                throw new ApplicationException("Unable to proceed. Hierarchy referred code Id '" +
                    crt.CodeID + "' is not present in codelist: " + clType.FullIdent);

            sb.Append("values(");
            sb.Append(ValChar(item.Id)).Append(',');
            sb.Append(ValChar(parentCode)).Append(',');
            sb.Append(ValChar((level + 1).ToString())).Append(',');
            foreach (string lg in LangSupport.SupportedLanguages)
                sb.Append(ValChar(item.Name.GetForLanguage(lg))).Append(',');

            CodeType ct = item as CodeType;
            foreach (string lg in LangSupport.SupportedLanguages)
                sb.Append(ValChar(ct != null ? ct.Description.GetForLanguage(lg) : string.Empty)).Append(',');
            sb.Append(ValChar(crt.ValidFrom)).Append(',');
            sb.Append(ValChar(crt.ValidTo)).Append(',');

            // Annotations ?
            if (anInventory.Dico.Count > 0)
                AddAnnotations(sb, item.AnnotationList);

            // Remove last comma
            sb.Remove(sb.Length - 1, 1);

            sb.Append(")").Append(Environment.NewLine);

            // Recurse
            foreach (CodeRefType it in crt.CodeRefList)
                FillHierarchyRow(sb, crt.CodeID, it, level + 1);
        }

        #endregion

        #region Helpers

        private string ValChar(string val)
        {
            if (val == null)
                return "''";

            val = val.Replace("'", "''");
            return "'" + val + "'";
        }

        private int GetLevelOfCode(CodeListType clt, string code)
        {
            int level = 0;
            CodeType c = clt.CodeList.LocateItem(code) as CodeType;
            do
            {
                if (c != null)
                {
                    ++level;
                    c = clt.CodeList.LocateItem(c.Parent) as CodeType;
                }
            } while (c != null);

            return level;
        }

        private void DefineTableFields(StringBuilder sb, bool extendConcept)
        {
            sb.Append("(Code char(10), Parent char(10), Level char(2), ");
            foreach (string lg in LangSupport.SupportedLanguages)
                sb.Append("Name_").Append(lg).Append(" char(120), ");

            foreach (string lg in LangSupport.SupportedLanguages)
                sb.Append("Desc_").Append(lg).Append(" char(2000), ");

            sb.Append("ValidFrom char(10), ValidTo char(10), ");

            if (extendConcept)
                sb.Append("CL_FID char(80), TF char(30), ");

            // here Annotations selected
            GenAnnotationHeader(sb);

            // Remove last comma
            sb.Remove(sb.Length - 2, 2);

            sb.Append(");").Append(Environment.NewLine);

        }
        #endregion

        #region ConceptScheme

        /// <summary> Gen sas code to export concepts in conceptscheme as table </summary>
        /// <param name="clt">conceptScheme artefact to export in SAS</param>
        /// <param name="tbName">Name of the SAS table to create</param>
        /// <param name="anInv">List of annotations with selected ones</param>
        /// <returns>string for SAS execution</returns>
        public string GenSpConcept(ConceptSchemeType cst, string tbName, AnnotationInventory anInv)
        {
            string sp = string.Empty;
            anInventory = anInv;
            if (cst != null)
            {
                StringBuilder sb = new StringBuilder(256);
                sb.Append("proc sql;   create table ").Append(tablePrefix).Append('.');
                sb.Append(tbName);

                DefineTableFields(sb, true);
                sb.Append("insert into ").Append(tablePrefix).Append('.').Append(tbName).Append(Environment.NewLine);

                foreach (ConceptType c in cst.ConceptList)
                {
                    sb.Append("values(");
                    sb.Append(ValChar(c.Id)).Append(',');
                    sb.Append(ValChar(c.Parent)).Append(',');
                    sb.Append(ValChar(GetLevelOfConcept(cst, c.Id).ToString())).Append(',');
                    foreach (string lg in LangSupport.SupportedLanguages)
                        sb.Append(ValChar(c.Name.GetForLanguage(lg))).Append(',');

                    foreach (string lg in LangSupport.SupportedLanguages)
                        sb.Append(ValChar(c.Description.GetForLanguage(lg))).Append(',');

                    sb.Append(ValChar(cst.ValidFrom)).Append(',');
                    sb.Append(ValChar(cst.ValidTo)).Append(',');

                    // Codelist Ref and type
                    string tf = string.Empty;
                    if (c.CoreRepresentation.IsFilled)
                    {
                        sb.Append(ValChar(c.CoreRepresentation.FullIdent)).Append(',');
                        tf = "CodeList";
                    }
                    else
                    {
                        sb.Append(ValChar(string.Empty)).Append(',');
                        tf = c.TextFormat.ToString();
                    }

                    // Text format
                    sb.Append(ValChar(tf)).Append(',');

                    // Annotations ?
                    if (anInventory.Dico.Count > 0)
                        AddAnnotations(sb, c.AnnotationList);

                    // Remove last comma
                    sb.Remove(sb.Length - 1, 1);

                    sb.Append(")").Append(Environment.NewLine);
                }
                sb.Append(";");
                sb.Append("quit;");

                sp = sb.ToString();
            }

            return sp;
        }

        private int GetLevelOfConcept(ConceptSchemeType cst, string id)
        {
            int level = 0;
            ConceptType c = cst.ConceptList.LocateItem(id) as ConceptType;
            do
            {
                if (c != null)
                {
                    ++level;
                    c = cst.ConceptList.LocateItem(c.Parent) as ConceptType;
                }
            } while (c != null);

            return level;
        }


        #endregion
    }
}

       
